
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLastSubmittedCOI]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLastSubmittedCOI]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetLastSubmittedCOI
* Purpose:		Retrieves the last submitted COI for the specified person
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @admPersonId		Person Id
******************************************************************/

CREATE Procedure [dbo].[GetLastSubmittedCOI] (
		@admPersonId		INTEGER	
)
AS
BEGIN

	SET NOCOUNT ON
	
	
	
	SELECT TOP 1 COI.Id,
		AdmPersonId,
		FwkDomainUserId,
		LastNotified,
		LastModified,
		DateSubmitted,
		DateReviewed,		
		HasOutsideActivities,
		HasEquityInterests,
		HasFamilyAssociations,
		Royalty,
		OtherActivity,
		PolicyViolation,
		AggregateEquity,
		[Status],
		RefCode.RefMeaning AS StatusMeaning, 
		DocumentId,
		ReportingPeriodId,
		ReportingPeriod,
		COI.COITypeId
	FROM ConflictOfInterest COI INNER JOIN RefCode ON COI.[Status] = RefCode.RefCode
		LEFT JOIN COIReportingPeriods CRP ON CRP.Id = COI.ReportingPeriodId
	WHERE AdmPersonId = @admPersonId
	AND [STATUS] in ('COISTATUS_SUBMITTED', 'COISTATUS_REVIEWED')
	ORDER BY DateSubmitted DESC
	
END


GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  